import pandas as pd
import os
import json

def process_manifest(manifest_path):
    """
    Processes the manifest.json file to extract text file URLs and item IDs.
    """
    print("Processing manifest file...")
    with open(manifest_path, 'r') as f:
        manifest_data = json.load(f)
    
    rows = manifest_data.get('rows', [])
    if not rows:
        print("ERROR: 'rows' key not found in manifest or is empty.")
        return pd.DataFrame(columns=['id', 'text_url'])

    processed_records = []
    for record in rows:
        # According to the 'cols' key: ['filename', 'item_id', 'md5', 'size', 'object_key']
        file_name = record[0]
        item_url = record[1]
        object_key = record[4]
        
        if file_name.endswith('.txt'):
            item_id = item_url.strip('/').split('/')[-1]
            # The full URL is https://<object_key>
            full_text_url = f"https://{object_key}"
            processed_records.append({'id': item_id, 'text_url': full_text_url})
            
    print(f"Found {len(processed_records)} text file records in manifest.")
    return pd.DataFrame(processed_records)

def inspect_and_merge_data():
    """
    Loads both metadata and manifest, merges them, and prints a summary.
    """
    base_path = os.path.join(os.path.dirname(__file__), '..', 'data')
    meta_path = os.path.join(base_path, 'metadata.csv')
    manifest_path = os.path.join(base_path, 'manifest.json')

    try:
        print(f"Loading metadata from {meta_path}...")
        meta_df = pd.read_csv(meta_path, low_memory=False)
        # The 'id' in metadata is the full item URL, which is what we need to extract the ID from.
        # Let's rename it to avoid confusion before creating the join key.
        meta_df.rename(columns={'id': 'item_url'}, inplace=True)
        meta_df['id'] = meta_df['item_url'].str.strip('/').str.split('/').str[-1]
        print(f"Loaded {len(meta_df)} records from metadata.")
        
        manifest_df = process_manifest(manifest_path)

        if manifest_df.empty:
            print("\nManifest DataFrame is empty. Cannot merge. Aborting.")
            return

        print("\nMerging metadata and manifest dataframes on 'id'...")
        
        merged_df = pd.merge(meta_df, manifest_df, on='id', how='inner')
        
        print(f"\nSuccessfully merged. Total records in merged dataframe: {len(merged_df)}")
        
        print("\n--- Merged Data Summary ---")
        
        # Define the columns we want to see in the final summary
        summary_cols = ['id', 'title', 'contributor', 'text_url']
        print(f"Columns in merged dataframe (showing a subset): {summary_cols}")
        
        print("\nFirst 5 rows of merged data:")
        print(merged_df[summary_cols].head())
        
        print("\n--- End of Summary ---")

    except FileNotFoundError as e:
        print(f"Error: A file was not found. {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")

if __name__ == "__main__":
    inspect_and_merge_data()
 